﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DB
{
    public class DbShow : IDbShow
    {
        private DbConnection con;
        private SqlCommand dbCmd;

        public DbShow()
        {
            con = DbConnection.GetInstance();
            dbCmd = null;
        }

        public int GetShowId(Show showObj)
        {
            int showId = -1;
            string select = "SELECT showId " +
                            "FROM Show_xp " +
                            "WHERE date = @datetime AND cName = @cinemaHall";
            Console.WriteLine(select);

            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@datetime", showObj.DateTimeStart);
                dbCmd.Parameters.AddWithValue("@cinemaHall", showObj.CinemaHall.Name);

                showId = (Int32)dbCmd.ExecuteScalar();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine(sqlE.Message);
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            
            con.CloseConnection();
            
            return showId;
        }
        public int GetNextID() // til at hjælpe, skal ikke bruges under movie
        {

            String query = null;
            int nextInt = 1;
            try
            {
                query = "select MAX(showId) from Show_xp";
                SqlCommand command = con.GetDbCommand(query);
                nextInt = (Int32)command.ExecuteScalar();
                nextInt++;
                
            }
            catch (Exception e)
            {
            }
            con.CloseConnection();
            return nextInt;

        }

        public int InsertShow(Show show, string serialNo, string cName)
        {
            Console.WriteLine("databasejalla" + show.PricePerTicket);
            //finds reference objects before a connection is created
            IDbMovie dbmovie = new DbMovie();
            IDbCinemaHall dbcinema = new DbCinemaHall();
            string serial = dbmovie.FindMovie(serialNo).SerialNo;
            string hallname = dbcinema.FindCinemaHallNew(cName, false).Name;
            //******************************
            int rc = -1;
            string insert = null;
            insert = @"INSERT INTO Show_xp VALUES(@showId, @date, @serialNo, @cName, @dateEnd,@pricePerTicket)";
           // Console.WriteLine(insert);
            
            int nextShowId = GetNextID();
            //Console.WriteLine("nextId fundet: " + nextShowId);


            try
            {
                Console.WriteLine(show.PricePerTicket);
                dbCmd = con.GetDbCommand(insert);
                dbCmd.Parameters.AddWithValue("@showId", nextShowId);
                dbCmd.Parameters.AddWithValue("@date", show.DateTimeStart);
                dbCmd.Parameters.AddWithValue("@serialNo", serial); // Finder et showobjekt og derefter showID
                dbCmd.Parameters.AddWithValue("@cName", hallname);
                dbCmd.Parameters.AddWithValue("@dateEnd", show.DateTimeEnd);
                dbCmd.Parameters.AddWithValue("@pricePerTicket", Convert.ToDecimal(show.PricePerTicket));
                Console.WriteLine(Convert.ToDecimal(show.PricePerTicket));
                rc = dbCmd.ExecuteNonQuery();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("InsertShow(...) - SQL Error");
                Console.WriteLine(sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("InsertShow(...) - Exception Error");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return rc;
        }
        public int DeleteShow(DateTime dateTime, string cName)
        {
            String query = null;
            IDbCinemaHall dbcinema = new DbCinemaHall();
            int rc = -1;
            try
            {
                query = @"DELETE FROM Show_xp WHERE date = @date AND cName = @cName";
                Console.WriteLine(query);
                SqlCommand command = con.GetDbCommand(query);
                command.Parameters.AddWithValue("@date", dateTime);
                command.Parameters.AddWithValue("@cName", cName);
                rc = command.ExecuteNonQuery();
            }
            catch (SqlException sqle)
            {
                throw sqle;
            }
            catch (Exception e)
            {
                throw e;
            }

                con.CloseConnection();

            return rc; 
        }

        public Show FindShow(DateTime dateTimeStart, string cName, bool retriveAssociation)
        {
            SqlDataReader dbReader = null;
            Show showObj = null;

            string select = "SELECT date, serialNo, cName, dateEnd,pricePerTicket "
                          + "FROM Show_xp "
                          + "WHERE date = @date AND cName = @cName";

            try
            {
                dbCmd = con.GetDbCommand(select);
                dbCmd.Parameters.AddWithValue("@date", dateTimeStart);
                dbCmd.Parameters.AddWithValue("@cName", cName);

                dbReader = dbCmd.ExecuteReader();

                if (dbReader.Read())
                {
                    showObj = BuildShow(dbReader);
                }
                else
                {
                    showObj = null;
                }
                dbReader.Close();

                if (retriveAssociation && showObj != null)
                {
                    IDbMovie dbMovie = new DbMovie();
                    string serialNo = showObj.ThisMovie.SerialNo;
                    showObj.ThisMovie = dbMovie.FindMovie(serialNo);

                    IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                    showObj.CinemaHall = dbCinemaHall.FindCinemaHallNew(cName, true); // Alle cinemahalls seats med.

                    IDbSeat dbSeats = new DbSeat();
                    showObj.SeatsNotAvailable = dbSeats.FindSeatsTaken(showObj, true);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbShow - FindShow");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbShow - FindShow");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();

            return showObj;
        }
        public List<Show> FindAllShows(bool retriveAssociation)
        {
            List<Show> showsThisDate = new List<Show>();
            SqlDataReader dbReader = null;
            Show showObj = null;

            string selectShows = "SELECT * FROM Show_xp";

            //Console.WriteLine(selectShows);

            try
            {
                dbCmd = con.GetDbCommand(selectShows);
                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    showObj = BuildShow(dbReader);
                    showsThisDate.Add(showObj);
                }

                dbReader.Close();

                if(retriveAssociation)
                {

                    foreach (Show showObj2 in showsThisDate)
                    {
                        string serialNo = showObj2.ThisMovie.SerialNo;
                        IDbMovie dbMovie = new DbMovie();
                        Movie movieObj = dbMovie.FindMovie(serialNo); // Associering til movie så man kan se review?
                        showObj2.ThisMovie = movieObj;

                        //string cinemaHallName = showObj2.CinemaHall.Name;
                        //IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                        //CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, true); // associering fra cinemahall til dens seats
                        //showObj2.CinemaHall = cinemaHallObj;

                        //IDbSeat dbSeats = new DbSeat();
                        //showObj2.SeatsNotAvailable = dbSeats.FindSeatsTaken(showObj2, true);// liste

                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbShow");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbShow");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return showsThisDate;
        }

        public List<Show> GetAllShowsThisDate(DateTime dateTime, bool retriveAssociation)
        {
            List<Show> showsThisDate = new List<Show>();
            SqlDataReader dbReader = null;
            Show showObj = null;

            string selectShows = "SELECT date, serialNo, cName, dateEnd, pricePerTicket "
                               + "FROM Show_xp "
                               + "WHERE DAY(date) = @day AND MONTH(date) = @month AND YEAR(date) = @year";
                               //+ "WHERE date = @date";
            
            Console.WriteLine(selectShows);

            try
            {
                dbCmd = con.GetDbCommand(selectShows);
                dbCmd.Parameters.AddWithValue("@day", dateTime.Day);
                dbCmd.Parameters.AddWithValue("@month", dateTime.Month);
                dbCmd.Parameters.AddWithValue("@year", dateTime.Year);

                dbReader = dbCmd.ExecuteReader();

                while (dbReader.Read())
                {
                    showObj = BuildShow(dbReader);
                    showsThisDate.Add(showObj);
                }

                dbReader.Close();

                if (retriveAssociation)
                {
                    foreach (Show showObj2 in showsThisDate)
                    {
                        string serialNo = showObj2.ThisMovie.SerialNo;
                        IDbMovie dbMovie = new DbMovie();
                        Movie movieObj = dbMovie.FindMovie(serialNo); // Associering til movie så man kan se review?
                        showObj2.ThisMovie = movieObj;

                        string cinemaHallName = showObj2.CinemaHall.Name;
                        IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                        CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, true); // associering fra cinemahall til dens seats
                        showObj2.CinemaHall = cinemaHallObj;

                        IDbSeat dbSeats = new DbSeat();
                        showObj2.SeatsNotAvailable = dbSeats.FindSeatsTaken(showObj2, true);// liste
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbShow");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbShow");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return showsThisDate;
        }

        public Show FindShowWithId(int showId, bool retriveAssociation)
        {
            SqlDataReader dbReader = null;
            Show showObj = null;

            string selectShows = "SELECT date, serialNo, cName, dateEnd,pricePerTicket "
                               + "FROM Show_xp "
                               + "WHERE showId = @showId";

            //Console.WriteLine(selectShows);

            try
            {
                dbCmd = con.GetDbCommand(selectShows);
                dbCmd.Parameters.AddWithValue("@showId", showId);
                dbReader = dbCmd.ExecuteReader();

                if (dbReader.Read())
                {
                    showObj = BuildShow(dbReader);
                }
                else
                {
                    showObj = null;
                }

                dbReader.Close();

                if (retriveAssociation)
                {
                    string serialNo = showObj.ThisMovie.SerialNo;
                    IDbMovie dbMovie = new DbMovie();
                    Movie movieObj = dbMovie.FindMovie(serialNo); // Associering til movie så man kan se review?
                    showObj.ThisMovie = movieObj;

                    string cinemaHallName = showObj.CinemaHall.Name;
                    IDbCinemaHall dbCinemaHall = new DbCinemaHall();
                    CinemaHall cinemaHallObj = dbCinemaHall.FindCinemaHallNew(cinemaHallName, true); // associering fra cinemahall til dens seats
                    showObj.CinemaHall = cinemaHallObj;

                    IDbSeat dbSeats = new DbSeat();
                    showObj.SeatsNotAvailable = dbSeats.FindSeatsTaken(showObj, true);// liste
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("sql Error in DbShow");
                Console.WriteLine(e.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in DbShow");
                Console.WriteLine(e.Message);
            }

            con.CloseConnection();
            return showObj;
        }

        private Show BuildShow(SqlDataReader dbReader)
        {
            Show showObj = new Show();
            Movie movieObj = new Movie();
            CinemaHall cinemaHallObj = new CinemaHall();
            DbMovie dbMovie = new DbMovie();
            DbCinemaHall dbCinema = new DbCinemaHall();

            try
            {
                showObj.DateTimeStart = (DateTime)dbReader["date"];

                movieObj.SerialNo = (String)dbReader["serialNo"];
                cinemaHallObj.Name = (String)dbReader["cName"];
                showObj.ThisMovie = movieObj;
                showObj.CinemaHall = cinemaHallObj;

                showObj.DateTimeEnd = (DateTime)dbReader["dateEnd"];
                Double newDouble = Convert.ToDouble(dbReader["pricePerTicket"]);
                showObj.PricePerTicket = newDouble;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in building showObj");
                Console.WriteLine(e.Message);
            }

            return showObj;
        }
    
    }
}
